
clear all 
set more off
global dir "C:\Users\yuanzi\Quant Space&Trade Dropbox\Yuan Zi (资源)\1-Projects\0-RRed\1-Shipment cargo\Ais project"
cd "$dir\Get ship_port_to_port.dta\raw Data"


** (1)Drop anchorage and in transit
use ais_container_cleaned,clear
drop if port_type=="A"  
drop if inTransit=="true"
save temp,replace

** (2)Get Ship-port(i)-port(j) Data

			*(4.1) Reshape data to s-i-j		
			use temp,clear
		    gsort ship_id time move_type
			by ship_id: gen id=_n
			drop if id==1 & move_type=="arrival" //drop obs without depature couterparts
            
			drop id
			by ship_id: gen id=_n
			gen odd = mod(id,2) 
			replace id=id+1 if odd==1
			cap drop inT mmsi imo shipname type_name flag time* odd port_type
			*duplicates drop teu ballastcapt draughtm ship_id port_id port_name move_type lon lat new_draught speed course country dwt draught_min draught_max date travel_type nn NN travel_hs, force
			save temp,replace

			use temp,clear
			keep if move_type=="departure"
			keep ship_id port* lon lat new_draught country date id inTransit port_type  trip
			rename trip departure_trip_id
			rename country D_country
			rename port* D_port*
			rename lon D_lon
			rename lat D_lat
			rename new* D_new*
			rename date D_date
			rename inTransit D_inTransit
			save temp1,replace

			use temp,clear
			drop if move_type=="departure"
			keep ship_id port* lon lat new_draught country date id travel_hs d* inTransit port_type  trip
			rename trip arrive_trip_id
			rename country A_country
			rename port* A_port*
			rename lon A_lon
			rename lat A_lat
			rename new* A_new*
			rename date A_date
			rename inTransit A_inTransit
			save temp2,replace

			use temp2,clear
			merge 1:1 ship_id id using temp1
			distinct ship_id  //checked, last departure date dont have arrival info, not merged =distinct ship_id
			keep if _merge==3  
			drop _merge id
			order ship_id A* D*  
			save temp,replace
			
**(3) Draught of a travel= arrival drft, if missing use departure draught
			gen draught_trv= A_new
			replace draught_trv=D_new if draught_trv==.  //basically zero being used
			save temp,replace
			
**(4) Merge with ship_specific chara
            use temp,clear
			merge m:1 ship_id using ship_all_variables
			keep if _merge==3 // should be zero droped
			drop _merge
			save temp,replace
			
			**check
			destring draughtm,replace
			replace draughtm=draughtm*10
			sum ship_id if draught_trv==.  //5472 out of  22,775  is missing
			sum ship_id if draughtm==.  //2,626     out of  22,775 is missing
			sum ship_id if draughtm==. & draught_trv==.  //2,626  overlap
			 //hence to compute actual flow, 4556+2515-1714 will be missing either draughtm, or draught_trv(about 1.2% obs)
			egen draught_s=rowmax(draughtm draught_max) //227 missing
			label var draught_s "scantling draught"
			 //still kept those missing obs, set their correspondent ballast and dwt_usage to missing
			replace draught_trv=draught_s if  draught_trv>draught_s & draught_trv!=.
			
**(5) Set travels with draught<0.5, 0.55,0.6,0.65(designed draught) as ballast 
			gen temp=.
			local cutoff "50 55 60 65"
			foreach i of local cutoff{
			replace temp=`i'*draught_s/100  //temp=ballast draught
			gen ballast_`i'=1 if draught_trv<=temp
			replace ballast_`i'=0 if ballast_`i'==.
			replace ballast_`i'=. if draught_s==.  //if draughtm =0, will be missing
			
	
			*(5) caculate effective dwt
			gen dwt_usage_`i'=dwt*(draught_trv-temp)/(draught_s-temp)  //
			replace dwt_usage_`i'=0 if ballast_`i'==1 //if dwt, draught_tv, or draughtm =0, will be missing
		
			
			*(6) caculate effective teu
			destring teu, force replace   //23,221 missing out of 430,467; about 5%
			gen teu_usage_`i'=teu*(draught_trv-temp)/(draught_s-temp)   //
			replace teu_usage_`i'=0 if ballast_`i'==1 //if dwt, draught_tv, or draughtm =0, will be missing
			}
			
			drop temp  draught_max
			
			*label var draughtm "designed draught 10cm" 
			*label var teu_usage "effective box travel"
			*label var dwt_usage "effective dwt travel"
			drop if A_port_id==D_port_id //checked, zero observation deleted
			cap drop id
			
			label var travel_hs "including zero, use with caution"
			save ship_port_to_port,replace
			
			save "$dir\Repository\data_intermediate\ship_port_to_port",replace 

			
*********************Clean************************
           !del *tmp*.dta
		   !del *temp*.dta
		   
		   
		   local data "ID_ship_list_full ais_container_sorted_midstep ais_container_sorted_yz  port_list_full ship_all_variables trip_list_full ais_container_cleaned"
			foreach i of local data{
			cap erase `i'.dta
			}


		*(3) Clean Intermediate Temp Files 
		cap erase Clarkson0.dta
		cap erase data.dta
		cap erase ship_list_full.dta
		cap erase ais_container_sorted_yz.dta
		cap erase ship_port_to_port.dta

			
